SQL は、特定の処理を行う際にデータの型が同一でないとエラーが発生しますが、もとのスキーマを紹介するよりももっとお手軽にカラムの型を確認したいときがありませんか?
例えば、出力結果を見ただけでは、12345 が STRING なのか INT64 なのか判別不可能ですよね。(もし判別可能な方法知っている人いたら教えて下さい…)
なのでお手軽に BigQuery の結果の型を確認したい時になにか良い方法がないかなと調べていたら、
OSS でbqutil.fnという UDF が GCP から提供されていることを知った。
このbqutil.fn はbigquery-utils/udfs/community/のディレクトリに格納されている UDF がbqutil という GCP プロジェクトのfn データセットに同期されているので、
例えば型の確認の場合
bqutil.fn.typeof()で、どの GCP プロジェクトの Google BigQuery から実行しても bqutil.fn.typeof()を実行可能にしているらしい。
良い仕組みだ
This directory contains community contributed user-defined functions to extend BigQuery for more specialized usage patterns. Each UDF within this directory will be automatically synchronized to the bqutil project within the fn dataset for reference in queries.
実際に以下のクエリを Google BigQuery で実行すると
SELECT
bqutil.fn.typeof(""),
bqutil.fn.typeof(b""),
bqutil.fn.typeof(1.0),
bqutil.fn.typeof(STRUCT()),STRING
BYTES
FLOAT64
STRUCT
の結果が出力され、各カラムのデータの型を確認できる。 便利!
bqutil.fn.typeof() の UDF の実態としては
bigquery-utils/udfs/community/typeof.sqlxが参照されて実行されている。
他にも
- URL の key を抽出できるbqutil.fn.url_keys()
- ランダムな値を出力できるbqutil.fn.url_keys()
など、自前で正規表現で頑張って書いているけど実際は OSS の UDF として公開されているケースも多々ありそうなかゆいところに手が届く UDF が公開されていた。
変わり種としては、 StatsLib: Statistical UDFsという統計的な処理を行う UDF も公開されていた。
線形回帰や p 値の計算ができる UDF も公開されており、面白かった。